/*
 	
*/
ALTER PROC SP_SAVE_CREDIT_NOTE_LINEITEMS @CN_ID 	INT, 
					@LINEITEM_ID 	INT,
					@DISCOUNT_PER_UNIT FLOAT,
					@DISCOUNT_AMOUNT FLOAT,
					@REASON 	VARCHAR(100),
					@USER_ID	VARCHAR(20),
					@ADJUSTMENT_QTY INT,
					@IS_CANCEL	INT
AS
BEGIN
	DECLARE @CNLT_ID INT
	SELECT @CNLT_ID = CNLT_ID FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND LINEITEM_ID = @LINEITEM_ID

	IF ( @CNLT_ID IS NULL OR @CNLT_ID <= 0 ) -- NEW LINE ITEM
	 BEGIN
		INSERT INTO CREDIT_NOTE_LINE_ITEM_TABLE ( CN_ID, LINEITEM_ID, DISCOUNT_PER_UNIT, DISCOUNT_AMOUNT, STATUS, REASON, ADJUSTMENT_QTY )
			VALUES ( @CN_ID, @LINEITEM_ID, @DISCOUNT_PER_UNIT, @DISCOUNT_AMOUNT, 'HOLD', @REASON, @ADJUSTMENT_QTY )
		SELECT @CNLT_ID = @@IDENTITY
	
		INSERT INTO CREDIT_NOTE_HOLDS_TABLE( CNLT_ID, HOLD_BY_GROUP, HOLD_REASON_CODE, HOLD_BY_USER, TIMESTAMP ) 
		               VALUES( @CNLT_ID, 'OPERATIONS',  '0', @USER_ID, CURRENT_TIMESTAMP )	 
		
		INSERT INTO CREDIT_NOTE_HOLDS_TABLE( CNLT_ID, HOLD_BY_GROUP, HOLD_REASON_CODE, HOLD_BY_USER, TIMESTAMP ) 
		               VALUES( @CNLT_ID, 'MARKETING',  '0', @USER_ID, CURRENT_TIMESTAMP )
	
		INSERT INTO CREDIT_NOTE_HOLDS_TABLE( CNLT_ID, HOLD_BY_GROUP, HOLD_REASON_CODE, HOLD_BY_USER, TIMESTAMP ) 
		               VALUES( @CNLT_ID, 'SALES',  '0', @USER_ID, CURRENT_TIMESTAMP )
	
		INSERT INTO CREDIT_NOTE_HOLDS_TABLE( CNLT_ID, HOLD_BY_GROUP, HOLD_REASON_CODE, HOLD_BY_USER, TIMESTAMP ) 
			               VALUES( @CNLT_ID, 'FINANCE',  '0', @USER_ID, CURRENT_TIMESTAMP )	
	 END
	ELSE --NEED TO UPDATE
	 BEGIN
		DECLARE @DB_DISCOUNT_PER_UNIT INT
		DECLARE @DB_DISCOUNT_AMOUNT FLOAT
		DECLARE @DB_REASON 	VARCHAR(100)
		DECLARE @DB_ADJUSTMENT_QTY INT

		SELECT @DB_DISCOUNT_PER_UNIT = DISCOUNT_PER_UNIT, @DB_DISCOUNT_AMOUNT = DISCOUNT_AMOUNT, @DB_REASON = REASON, @DB_ADJUSTMENT_QTY = ADJUSTMENT_QTY FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CNLT_ID = @CNLT_ID

		IF ( ISNULL(@DB_DISCOUNT_PER_UNIT,'') <> ISNULL(@DISCOUNT_PER_UNIT,'') OR ISNULL(@DB_DISCOUNT_AMOUNT,'') <> ISNULL(@DISCOUNT_AMOUNT,'') OR ISNULL(@DB_REASON,'') <> ISNULL(@REASON,'') OR ISNULL(@DB_ADJUSTMENT_QTY,'') <> ISNULL(@ADJUSTMENT_QTY,'') )
		 BEGIN
			UPDATE CREDIT_NOTE_LINE_ITEM_TABLE SET DISCOUNT_PER_UNIT = @DISCOUNT_PER_UNIT, DISCOUNT_AMOUNT = @DISCOUNT_AMOUNT, 
				REASON = @REASON, ADJUSTMENT_QTY = @ADJUSTMENT_QTY, MODIFIED_BY = @USER_ID, MODIFIED_ON = CURRENT_TIMESTAMP
				WHERE CNLT_ID = @CNLT_ID
			IF ( ISNULL(@DB_DISCOUNT_PER_UNIT,'') <> ISNULL(@DISCOUNT_PER_UNIT,'') OR ISNULL(@DB_DISCOUNT_AMOUNT,'') <> ISNULL(@DISCOUNT_AMOUNT,'') OR ISNULL(@DB_ADJUSTMENT_QTY,'') <> ISNULL(@ADJUSTMENT_QTY,'') ) --QTY, PRICE, AMOUNT CHANGED NEED TO SET HOLDS
			 BEGIN
				UPDATE CREDIT_NOTE_TABLE SET STATUS = 'HOLD' WHERE CREDITNOTE_ID = @CN_ID
				UPDATE CREDIT_NOTE_LINE_ITEM_TABLE SET STATUS = 'HOLD' WHERE CNLT_ID = @CNLT_ID
				UPDATE CREDIT_NOTE_HOLDS_TABLE SET HOLD_REASON_CODE = '0', HOLD_BY_USER = @USER_ID, [TIMESTAMP] = CURRENT_TIMESTAMP WHERE CNLT_ID = @CNLT_ID
			 END
		 END	
		IF ( @IS_CANCEL = 0 ) --LINE ITEM IS CANCELLED
		 BEGIN
			UPDATE CREDIT_NOTE_LINE_ITEM_TABLE SET STATUS = 'CANCELLED' WHERE CNLT_ID = @CNLT_ID
			--APPROVING HOLDS FOR THE CANCELLED LINE ITEMS
			UPDATE CREDIT_NOTE_HOLDS_TABLE SET HOLD_REASON_CODE = '', HOLD_BY_USER = @USER_ID, [TIMESTAMP] = CURRENT_TIMESTAMP WHERE CNLT_ID = @CNLT_ID

			DECLARE @TOTAL_LINEITES INT
			DECLARE @CANCELLED_LINEITEMS_COUNT INT
			DECLARE @APPROVED_LINEITEMS_COUNT INT
			DECLARE @HOLD_LINEITEMS_COUNT INT	
		
			SELECT @TOTAL_LINEITES = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID			
			SELECT @CANCELLED_LINEITEMS_COUNT = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND STATUS = 'CANCELLED'
			SELECT @APPROVED_LINEITEMS_COUNT = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND STATUS = 'APPROVED'
			SELECT @HOLD_LINEITEMS_COUNT = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND STATUS = 'HOLD'
			
			IF ( ISNULL(@CANCELLED_LINEITEMS_COUNT,0) = @TOTAL_LINEITES ) --CREDIT NOTE IS CANCELLED
			 BEGIN
				UPDATE CREDIT_NOTE_TABLE SET STATUS = 'CANCELLED' WHERE CREDITNOTE_ID = @CN_ID
			 END
			ELSE IF ( (ISNULL(@CANCELLED_LINEITEMS_COUNT,0)+ISNULL(@APPROVED_LINEITEMS_COUNT,0)) = @TOTAL_LINEITES ) --CREDIT NOTE IS APPROVED
			 BEGIN
				UPDATE CREDIT_NOTE_TABLE SET STATUS = 'APPROVED' WHERE CREDITNOTE_ID = @CN_ID
			 END
			ELSE IF ( ISNULL(@HOLD_LINEITEMS_COUNT,0) > 0 ) --CREDIT NOTE IS IN HOLD
			 BEGIN
				UPDATE CREDIT_NOTE_TABLE SET STATUS = 'HOLD' WHERE CREDITNOTE_ID = @CN_ID
			 END
		 END	
	 END
	--DUMMY UPDATE
	UPDATE CREDIT_NOTE_TABLE SET STATUS = STATUS WHERE CREDITNOTE_ID = -1
END
					